﻿using EMS.Entities;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OracleClient;
using System.Configuration;

namespace EMS
{
    public partial class Home : DevExpress.XtraEditors.XtraForm
    {
        public const int WM_NCLBUTTONDOWN = 0xA1;
        public const int HT_CAPTION = 0x2;
        string connectionString = ConfigurationManager.ConnectionStrings["OrlcConnect"].ConnectionString;
        List<ElectricRecord> electricRecordList = new List<ElectricRecord>();
        List<ElectricRecord> electricRecordListLoading = new List<ElectricRecord>();
        List<CustomerView> customerView = new List<CustomerView>();

        List<int> comboYear = new List<int>();

        [DllImportAttribute("user32.dll")]
        public static extern int SendMessage(IntPtr hWnd,
                         int Msg, int wParam, int lParam);
        [DllImportAttribute("user32.dll")]
        public static extern bool ReleaseCapture();
        public Staff staff = new Staff();
        public string name { get; set; }
        public Home()
        {
            InitializeComponent();
        }

        private void Home_MouseDown(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                ReleaseCapture();
                SendMessage(Handle, WM_NCLBUTTONDOWN, HT_CAPTION, 0);
            }
        }

        protected override void OnFormClosed(FormClosedEventArgs e)
        {
            base.OnFormClosed(e);
            Application.Exit();
        }

        private void btnPrice_Click(object sender, EventArgs e)
        {
            this.Hide();
            (new Prices()).ShowDialog();
            this.Show();
        }

        private void Home_Load(object sender, EventArgs e)
        {
            Login login = new Login();
            login.loginConnect = new Login.passStaff(setStaff);
            login.ShowDialog();
            lblName.Text = "Welcome " + name;
            LoadMonthYear();
        }

        public void setStaff(Staff item, string name)
        {
            staff.Password = item.Password;
            staff.StaffId = item.StaffId;
            staff.UserName = item.UserName;
            this.name = name;
        }

        private void btnUser_Click(object sender, EventArgs e)
        {
            this.Hide();
            (new Users(staff.StaffId)).ShowDialog();
            this.Show();
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            if (checkExisted() == 0)
            {
                btnUpdate.Enabled = false;
                btnPaid.Enabled = false;
                btnSave.Enabled = true;
                int flag = 0;
                customerView.Clear();
                electricRecordListLoading.Clear();
                var connection = new OracleConnection(connectionString);
                try
                {
                    connection.Open();
                    int month = 1, year = 0;
                    if (DateTime.Today.Month == 1)
                    {
                        month = 12;
                        year = DateTime.Today.Year - 1;
                    }
                    else
                    {
                        month = DateTime.Today.Month - 1;
                        year = DateTime.Today.Year;
                    }


                    var electricRecordQuery = new OracleCommand()
                    {
                        Connection = connection,
                        CommandText = String.Format(@"SELECT customerid
                                    FROM electricrecord
                                    WHERE month = {0} AND year = {1}", month, year),
                        CommandType = CommandType.Text
                    };

                    OracleDataReader ElectricReader = electricRecordQuery.ExecuteReader();

                    if (ElectricReader.Read() != false)
                    {
                        flag = 1;
                    }

                    if (flag == 0)
                    {
                        var cmd = new OracleCommand()
                        {
                            Connection = connection,
                            CommandText = @"SELECT c.customerid, c.registerplace, p.name,c.CMND, t.typeid, t.price
                                    FROM customer c, type t, person p
                                    WHERE t.typeid = c.typeid AND p.personid = c.personid",
                            CommandType = CommandType.Text
                        };

                        OracleDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            customerView.Add(new CustomerView()
                            {
                                CustomerId = reader.GetInt32(0),
                                RegisterPlace = reader.GetInt32(1),
                                CustomerName = reader.GetString(2),
                                CMND = reader.GetString(3),
                                TypeId = reader.GetInt32(4),
                                Price = reader.GetFloat(5)
                            });
                        }


                        int monthSub = 1;
                        if (month == 12)
                        {
                            monthSub = 1;
                            year++;
                        }
                        else
                        {
                            monthSub = month + 1;
                        }
                        foreach (CustomerView i in customerView)
                        {

                            electricRecordListLoading.Add(new ElectricRecord
                            {
                                CustomerId = i.CustomerId,
                                CustomerName = i.CustomerName,
                                RegisterPlace = i.RegisterPlace,
                                CMND = i.CMND,
                                Month = monthSub,
                                Year = year,
                                Price = i.Price,
                                FirstPoint = 0,
                                LastPoint = 0
                            });
                        }

                        dgvRecords.DataSource = electricRecordListLoading;
                        dgvRecords.Columns.Remove("RegisterPlace");
                        dgvRecords.Columns.Remove("RecordId");
                    }
                    else
                    {
                        var cmd = new OracleCommand()
                        {
                            Connection = connection,
                            CommandText = String.Format(@"SELECT c.customerid, c.registerplace, p.name, e.lastpoint, c.CMND, t.typeid, t.price
                                    FROM customer c
                                    JOIN electricrecord e ON c.customerid = e.customerid
                                    JOIN type t ON c.typeid = t.typeid 
                                    JOIN person p ON p.personid = c.personid
                                    WHERE month = {0} AND year = {1}", month, year),
                            CommandType = CommandType.Text
                        };

                        var cmd01 = new OracleCommand()
                        {
                            Connection = connection,
                            CommandText = String.Format(@"SELECT distinct c.customerid, c.registerplace, p.name, c.CMND, t.typeid, t.price
                                    FROM customer c
                                    LEFT JOIN electricrecord e ON c.customerid = e.customerid
                                    JOIN type t ON c.typeid = t.typeid 
                                    JOIN person p ON p.personid = c.personid
                                    MINUS 
                                    SELECT c.customerid, c.registerplace, p.name, c.CMND, t.typeid, t.price
                                    FROM customer c
                                    JOIN electricrecord e ON c.customerid = e.customerid
                                    JOIN type t ON c.typeid = t.typeid 
                                    JOIN person p ON p.personid = c.personid
                                    WHERE month = {0} AND year = {1}", month, year),
                            CommandType = CommandType.Text
                        };

                        OracleDataReader reader01 = cmd01.ExecuteReader();
                        while (reader01.Read())
                        {
                            customerView.Add(new CustomerView()
                            {
                                CustomerId = reader01.GetInt32(0),
                                RegisterPlace = reader01.GetInt32(1),
                                CustomerName = reader01.GetString(2),
                                LastPoint = 0,
                                CMND = reader01.GetString(4),
                                TypeId = reader01.GetInt32(5),
                                Price = reader01.GetFloat(6)
                            });
                        }

                        OracleDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            customerView.Add(new CustomerView()
                            {
                                CustomerId = reader.GetInt32(0),
                                RegisterPlace = reader.GetInt32(1),
                                CustomerName = reader.GetString(2),
                                LastPoint = String.IsNullOrEmpty(reader.GetFloat(3).ToString()) ? 0 : reader.GetFloat(3),
                                CMND = reader.GetString(4),
                                TypeId = reader.GetInt32(5),
                                Price = reader.GetFloat(6)
                            });
                        }

                        int monthSub = 1;
                        if (month == 12)
                        {
                            monthSub = 1;
                            year++;
                        }
                        else
                        {
                            monthSub = month + 1;
                        }
                        foreach (CustomerView i in customerView)
                        {


                            electricRecordListLoading.Add(new ElectricRecord
                            {
                                CustomerId = i.CustomerId,
                                CustomerName = i.CustomerName,
                                CMND = i.CMND,
                                Month = monthSub,
                                Year = year,
                                Price = i.Price,
                                FirstPoint = i.LastPoint,
                                LastPoint = 0,
                                RegisterPlace = i.RegisterPlace
                            });
                        }

                        dgvRecords.DataSource = electricRecordListLoading.OrderBy(x => x.CustomerId).ToList();
                        dgvRecords.Columns[0].ReadOnly = true;
                        dgvRecords.Columns[1].ReadOnly = true;
                        dgvRecords.Columns[2].ReadOnly = true;
                        dgvRecords.Columns[3].ReadOnly = true;
                        dgvRecords.Columns[4].ReadOnly = true;
                        dgvRecords.Columns[5].ReadOnly = true;
                        dgvRecords.Columns[6].ReadOnly = true;
                        dgvRecords.Columns[8].ReadOnly = true;

                        dgvRecords.Columns.Remove("RegisterPlace");
                        dgvRecords.Columns.Remove("RecordId");
                    }


                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
            else
            {
                MessageBox.Show("Already Exsited !");
            }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {

            int flag = CheckRow();
            if (flag == 0)
            {
                var connection = new OracleConnection(connectionString);
                try
                {
                    connection.Open();

                    OracleCommand cmd = connection.CreateCommand();
                    OracleTransaction trans = connection.BeginTransaction();
                    cmd.Transaction = trans;
                    foreach (DataGridViewRow row in dgvRecords.Rows)
                    {
                        int CustomerId = int.Parse(row.Cells[0].Value.ToString());
                        //string CustomerName = row.Cells[1].Value.ToString();
                        //string CMND = row.Cells[2].Value.ToString();
                        int month = int.Parse(row.Cells[3].Value.ToString());
                        int year = int.Parse(row.Cells[4].Value.ToString());
                        float lastpoint = float.Parse(row.Cells[6].Value.ToString());
                        float firstpoint = float.Parse(row.Cells[5].Value.ToString());
                        float price = float.Parse(row.Cells[7].Value.ToString());
                        int RegisterPlace;
                        if (row.Cells[8].Value.ToString().Equals("Hai Chau"))
                        {
                            RegisterPlace = 1;
                        }
                        else
                        {
                            RegisterPlace = 2;
                        }

                        if (RegisterPlace == 1)
                        {
                            cmd.CommandText = String.Format(@"INSERT INTO electricrecord(customerid, month, year, firstpoint, lastpoint, price)
                                                              VALUES({0}, {1}, {2}, {3}, {4}, {5})",
                                                              CustomerId, month, year, firstpoint, lastpoint, price);
                            cmd.ExecuteNonQuery();

                        }
                        else
                        {
                            cmd.CommandText = String.Format(@"INSERT INTO electricrecord@dblink_elec(customerid, month, year, firstpoint, lastpoint, price)
                                                              VALUES({0}, {1}, {2}, {3}, {4}, {5})",
                                                              CustomerId, month, year, firstpoint, lastpoint, price);
                            cmd.ExecuteNonQuery();

                        }

                    }
                    trans.Commit();
                    LoadMonthYear();
                    MessageBox.Show("Save Record Successfully!");
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    connection.Close();
                }

            }
            else if (flag == 1)
            {
                MessageBox.Show("Lastpoint Cannot Equals To Zero");
            }
            else if (flag == 2)
            {
                MessageBox.Show("Lastpoint Must Be Greater Than FirstPoint");
            }
        }

        public int CheckRow()
        {
            foreach (DataGridViewRow row in dgvRecords.Rows)
            {
                float lastpoint = float.Parse(row.Cells[6].Value.ToString());
                float firstpoint = float.Parse(row.Cells[5].Value.ToString());

                if (lastpoint == 0)
                {
                    return 1;
                }
                else if (lastpoint < firstpoint)
                {
                    return 2;
                }
            }
            return 0;
        }

        public int checkExisted()
        {
            var connection = new OracleConnection(connectionString);
            try
            {
                connection.Open();
                int month = DateTime.Today.Month;
                int year = DateTime.Today.Year;

                var electricRecordQuery = new OracleCommand()
                {
                    Connection = connection,
                    CommandText = String.Format(@"SELECT customerid
                                    FROM electricrecord
                                    WHERE month = {0} AND year = {1}
                                    UNION
                                    SELECT customerid 
                                    FROM electricrecord@dblink_elec
                                    WHERE month = {1} AND year = {2}", month, year, month, year),
                    CommandType = CommandType.Text
                };

                OracleDataReader ElectricReader = electricRecordQuery.ExecuteReader();

                if (ElectricReader.Read() != false)
                {
                    return 1;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                connection.Close();
            }

            return 0;
        }

        public void LoadMonthYear()
        {
            cbbMonth.Items.Clear();
            cbbMonth.Items.Add("1");
            cbbMonth.Items.Add("2");
            cbbMonth.Items.Add("3");
            cbbMonth.Items.Add("4");
            cbbMonth.Items.Add("5");
            cbbMonth.Items.Add("6");
            cbbMonth.Items.Add("7");
            cbbMonth.Items.Add("8");
            cbbMonth.Items.Add("9");
            cbbMonth.Items.Add("10");
            cbbMonth.Items.Add("11");
            cbbMonth.Items.Add("12");

            cbbOffice.Items.Clear();
            cbbOffice.Items.Add("Hai Chau");
            cbbOffice.Items.Add("Thanh Khe");

            cbbYear.Items.Clear();
            var connection = new OracleConnection(connectionString);
            try
            {
                connection.Open();
                var cmd = new OracleCommand()
                {
                    Connection = connection,
                    CommandText = @"select distinct year from electricrecord
                                                        UNION
                                                        select distinct year from electricrecord@dblink_elec",
                    CommandType = CommandType.Text
                };

                OracleDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    cbbYear.Items.Add(reader.GetInt32(0));
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                connection.Close();
            }

        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                if (!String.IsNullOrEmpty(cbbMonth.SelectedItem.ToString()) || !String.IsNullOrEmpty(cbbYear.SelectedItem.ToString()))
                {
                    btnPaid.Enabled = true;
                    btnUpdate.Enabled = true;
                    btnSave.Enabled = false;

                    int month = int.Parse(cbbMonth.SelectedItem.ToString());
                    int year = int.Parse(cbbYear.SelectedItem.ToString());
                    string offce = "";
                    if (cbbOffice.SelectedItem != null)
                    {
                        offce = cbbOffice.SelectedItem.ToString();
                    }
                    List<ElectricRecord> electricRecordSearchList = new List<ElectricRecord>();

                    if (offce.Equals("Hai Chau"))
                    {
                        var connection = new OracleConnection(connectionString);
                        try
                        {
                            connection.Open();
                            var cmd = new OracleCommand()
                            {
                                Connection = connection,
                                CommandText = String.Format(@"SELECT e.recordid, c.customerid, c.registerplace, p.name, e.firstpoint, e.lastpoint, c.CMND, t.price
                                    FROM customer c JOIN person p ON c.personid = p.personid
                                    JOIN type t ON t.typeid = c.typeid
                                    JOIN electricrecord e ON e.customerid = c.customerid
                                    WHERE e.month = {0} and e.year = {1}", month, year),
                                CommandType = CommandType.Text
                            };

                            OracleDataReader reader = cmd.ExecuteReader();
                            while (reader.Read())
                            {
                                electricRecordSearchList.Add(new ElectricRecord()
                                {
                                    RecordId = reader.GetInt32(0),
                                    CustomerId = reader.GetInt32(1),
                                    RegisterPlace = reader.GetInt32(2),
                                    CustomerName = reader.GetString(3),
                                    FirstPoint = reader.GetFloat(4),
                                    LastPoint = reader.GetFloat(5),
                                    CMND = reader.GetString(6),
                                    Price = reader.GetFloat(7),
                                    Month = month,
                                    Year = year
                                });
                            }

                            dgvRecords.DataSource = electricRecordSearchList.OrderBy(x => x.CustomerId).ToList();
                            dgvRecords.Columns[0].ReadOnly = true;
                            dgvRecords.Columns[1].ReadOnly = true;
                            dgvRecords.Columns[2].ReadOnly = true;
                            dgvRecords.Columns[3].ReadOnly = true;
                            dgvRecords.Columns[4].ReadOnly = true;
                            dgvRecords.Columns[5].ReadOnly = true;
                            dgvRecords.Columns[6].ReadOnly = true;
                            dgvRecords.Columns[8].ReadOnly = true;
                            dgvRecords.Columns.Remove("RegisterPlace");
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                        finally
                        {
                            connection.Close();
                        }
                    }
                    else if (offce.Equals("Thanh Khe"))
                    {
                        var connection = new OracleConnection(connectionString);
                        try
                        {
                            connection.Open();
                            var cmd = new OracleCommand()
                            {
                                Connection = connection,
                                CommandText = String.Format(@"SELECT e.recordid, c.customerid, c.registerplace, p.name, e.firstpoint, e.lastpoint, c.CMND, t.price
                                    FROM customer c JOIN person p ON c.personid = p.personid
                                    JOIN type t ON t.typeid = c.typeid
                                    JOIN electricrecord@dblink_elec e ON e.customerid = c.customerid
                                    WHERE e.month = {0} and e.year = {1}", month, year),
                                CommandType = CommandType.Text
                            };

                            OracleDataReader reader = cmd.ExecuteReader();
                            while (reader.Read())
                            {
                                electricRecordSearchList.Add(new ElectricRecord()
                                {
                                    RecordId = reader.GetInt32(0),
                                    CustomerId = reader.GetInt32(1),
                                    RegisterPlace = reader.GetInt32(2),
                                    CustomerName = reader.GetString(3),
                                    FirstPoint = reader.GetFloat(4),
                                    LastPoint = reader.GetFloat(5),
                                    CMND = reader.GetString(6),
                                    Price = reader.GetFloat(7),
                                    Month = month,
                                    Year = year
                                });
                            }

                            dgvRecords.DataSource = electricRecordSearchList.OrderBy(x => x.CustomerId).ToList();
                            dgvRecords.Columns[0].ReadOnly = true;
                            dgvRecords.Columns[1].ReadOnly = true;
                            dgvRecords.Columns[2].ReadOnly = true;
                            dgvRecords.Columns[3].ReadOnly = true;
                            dgvRecords.Columns[4].ReadOnly = true;
                            dgvRecords.Columns[5].ReadOnly = true;
                            dgvRecords.Columns[6].ReadOnly = true;
                            dgvRecords.Columns[8].ReadOnly = true;

                            dgvRecords.Columns.Remove("RegisterPlace");
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                        finally
                        {
                            connection.Close();
                        }
                    }
                    else
                    {
                        var connection = new OracleConnection(connectionString);
                        try
                        {
                            connection.Open();
                            var cmd = new OracleCommand()
                            {
                                Connection = connection,
                                CommandText = String.Format(@"SELECT e.recordid, c.customerid, c.registerplace, p.name, e.firstpoint, e.lastpoint, c.CMND, t.price
                                    FROM customer c JOIN person p ON c.personid = p.personid
                                    JOIN type t ON t.typeid = c.typeid
                                    JOIN electricrecord e ON e.customerid = c.customerid
                                    WHERE e.month = {0} and e.year = {1}
                                    UNION
                                    SELECT e.recordid, c.customerid, c.registerplace, p.name, e.firstpoint, e.lastpoint, c.CMND, t.price
                                    FROM customer c JOIN person p ON c.personid = p.personid
                                    JOIN type t ON t.typeid = c.typeid
                                    JOIN electricrecord@dblink_elec e ON e.customerid = c.customerid
                                    WHERE e.month = {2} and e.year = {3}", month, year, month, year),
                                CommandType = CommandType.Text
                            };

                            OracleDataReader reader = cmd.ExecuteReader();
                            while (reader.Read())
                            {
                                electricRecordSearchList.Add(new ElectricRecord()
                                {
                                    RecordId = reader.GetInt32(0),
                                    CustomerId = reader.GetInt32(1),
                                    RegisterPlace = reader.GetInt32(2),
                                    CustomerName = reader.GetString(3),
                                    FirstPoint = reader.GetFloat(4),
                                    LastPoint = reader.GetFloat(5),
                                    CMND = reader.GetString(6),
                                    Price = reader.GetFloat(7),
                                    Month = month,
                                    Year = year
                                });
                            }

                            dgvRecords.DataSource = electricRecordSearchList.OrderBy(x => x.CustomerId).ToList();
                            dgvRecords.Columns[0].ReadOnly = true;
                            dgvRecords.Columns[1].ReadOnly = true;
                            dgvRecords.Columns[2].ReadOnly = true;
                            dgvRecords.Columns[3].ReadOnly = true;
                            dgvRecords.Columns[4].ReadOnly = true;
                            dgvRecords.Columns[5].ReadOnly = true;
                            dgvRecords.Columns[6].ReadOnly = true;
                            dgvRecords.Columns[8].ReadOnly = true;

                            dgvRecords.Columns.Remove("RegisterPlace");
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                        finally
                        {
                            connection.Close();
                        }
                    }
                    if (!String.IsNullOrEmpty(txtSearch.Text))
                    {
                        electricRecordSearchList.Clear();
                        foreach (DataGridViewRow row in dgvRecords.Rows)
                        {
                            if (row.Cells[2].Value.ToString().Contains(txtSearch.Text))
                            {
                                electricRecordSearchList.Add(new ElectricRecord()
                                {
                                    RecordId = int.Parse(row.Cells[0].Value.ToString()),
                                    CustomerId = int.Parse(row.Cells[1].Value.ToString()),
                                    RegisterPlace = row.Cells[9].Value.ToString() == "Hai Chau" ? 1 : 2,
                                    CustomerName = row.Cells[2].Value.ToString(),
                                    FirstPoint = float.Parse(row.Cells[6].Value.ToString()),
                                    LastPoint = float.Parse(row.Cells[7].Value.ToString()),
                                    CMND = row.Cells[3].Value.ToString(),
                                    Price = float.Parse(row.Cells[8].Value.ToString()),
                                    Month = int.Parse(row.Cells[4].Value.ToString()),
                                    Year = int.Parse(row.Cells[5].Value.ToString()),
                                    PaidDate = row.Cells[10].Value.ToString()
                                });
                            }
                        }
                        dgvRecords.DataSource = electricRecordSearchList.OrderBy(x => x.CustomerId).ToList();
                        dgvRecords.Columns[0].ReadOnly = true;
                        dgvRecords.Columns[1].ReadOnly = true;
                        dgvRecords.Columns[2].ReadOnly = true;
                        dgvRecords.Columns[3].ReadOnly = true;
                        dgvRecords.Columns[4].ReadOnly = true;
                        dgvRecords.Columns[5].ReadOnly = true;
                        dgvRecords.Columns[6].ReadOnly = true;
                        dgvRecords.Columns[8].ReadOnly = true;

                        dgvRecords.Columns.Remove("RegisterPlace");
                    }
                }
                else
                {
                    MessageBox.Show("Please choose Month and Year !");
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        private void dgvRecords_SelectionChanged(object sender, EventArgs e)
        {
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            if (dgvRecords.ColumnCount == 11)
            {
                int recordId = int.Parse(dgvRecords.CurrentRow.Cells[0].Value.ToString());
                int customerId = int.Parse(dgvRecords.CurrentRow.Cells[1].Value.ToString());
                string customerName = dgvRecords.CurrentRow.Cells[2].Value.ToString();
                int month = int.Parse(dgvRecords.CurrentRow.Cells[4].Value.ToString());
                int year = int.Parse(dgvRecords.CurrentRow.Cells[5].Value.ToString());
                float firstPoint = float.Parse(dgvRecords.CurrentRow.Cells[6].Value.ToString());
                float lastPoint = float.Parse(dgvRecords.CurrentRow.Cells[7].Value.ToString());
                string registerPlace = dgvRecords.CurrentRow.Cells[9].Value.ToString();
                float price = float.Parse(dgvRecords.CurrentRow.Cells[8].Value.ToString());
                this.Hide();
                (new UpdateOrPayment(recordId, customerId, customerName, month, year, firstPoint, lastPoint, registerPlace, price)).ShowDialog();
                dgvRecords.DataSource = null;
                this.Show();
            }
        }

        private void btnPaid_Click(object sender, EventArgs e)
        {
            if (!String.IsNullOrEmpty(cbbMonth.SelectedItem.ToString()) || !String.IsNullOrEmpty(cbbYear.SelectedItem.ToString()))
            {
                btnPaid.Enabled = true;
                btnUpdate.Enabled = true;
                btnSave.Enabled = false;

                int month = int.Parse(cbbMonth.SelectedItem.ToString());
                int year = int.Parse(cbbYear.SelectedItem.ToString());
                string offce = "";
                if (cbbOffice.SelectedItem != null)
                {
                    offce = cbbOffice.SelectedItem.ToString();
                }
                List<ElectricRecord> electricRecordSearchList = new List<ElectricRecord>();

                if (offce.Equals("Hai Chau"))
                {
                    var connection = new OracleConnection(connectionString);
                    try
                    {
                        connection.Open();
                        var cmd = new OracleCommand()
                        {
                            Connection = connection,
                            CommandText = String.Format(@"SELECT e.recordid, c.customerid, c.registerplace, p.name, e.firstpoint, e.lastpoint, c.CMND, t.price, pa.datepaid
                                    FROM customer c JOIN person p ON c.personid = p.personid
                                    JOIN type t ON t.typeid = c.typeid
                                    JOIN electricrecord e ON e.customerid = c.customerid
                                    JOIN payment pa ON pa.recordid = e.recordid
                                    WHERE e.month = {0} and e.year = {1}", month, year),
                            CommandType = CommandType.Text
                        };

                        OracleDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            electricRecordSearchList.Add(new ElectricRecord()
                            {
                                RecordId = reader.GetInt32(0),
                                CustomerId = reader.GetInt32(1),
                                RegisterPlace = reader.GetInt32(2),
                                CustomerName = reader.GetString(3),
                                FirstPoint = reader.GetFloat(4),
                                LastPoint = reader.GetFloat(5),
                                CMND = reader.GetString(6),
                                Price = reader.GetFloat(7),
                                PaidDate = reader.GetString(8),
                                Month = month,
                                Year = year
                            });
                        }

                        dgvRecords.DataSource = electricRecordSearchList.OrderBy(x => x.CustomerId).ToList();
                        dgvRecords.Columns[0].ReadOnly = true;
                        dgvRecords.Columns[1].ReadOnly = true;
                        dgvRecords.Columns[2].ReadOnly = true;
                        dgvRecords.Columns[3].ReadOnly = true;
                        dgvRecords.Columns[4].ReadOnly = true;
                        dgvRecords.Columns[5].ReadOnly = true;
                        dgvRecords.Columns[6].ReadOnly = true;
                        dgvRecords.Columns[8].ReadOnly = true;
                        dgvRecords.Columns.Remove("RegisterPlace");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
                else if (offce.Equals("Thanh Khe"))
                {
                    var connection = new OracleConnection(connectionString);
                    try
                    {
                        connection.Open();
                        var cmd = new OracleCommand()
                        {
                            Connection = connection,
                            CommandText = String.Format(@"SELECT e.recordid, c.customerid, c.registerplace, p.name, e.firstpoint, e.lastpoint, c.CMND, t.price, pa.datepaid
                                    FROM customer c JOIN person p ON c.personid = p.personid
                                    JOIN type t ON t.typeid = c.typeid
                                    JOIN electricrecord@dblink_elec e ON e.customerid = c.customerid
                                    JOIN payment@dblink_elec pa ON pa.recordid = e.recordid
                                    WHERE e.month = {0} and e.year = {1}", month, year),
                            CommandType = CommandType.Text
                        };

                        OracleDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            electricRecordSearchList.Add(new ElectricRecord()
                            {
                                RecordId = reader.GetInt32(0),
                                CustomerId = reader.GetInt32(1),
                                RegisterPlace = reader.GetInt32(2),
                                CustomerName = reader.GetString(3),
                                FirstPoint = reader.GetFloat(4),
                                LastPoint = reader.GetFloat(5),
                                CMND = reader.GetString(6),
                                Price = reader.GetFloat(7),
                                PaidDate = reader.GetString(8),
                                Month = month,
                                Year = year
                            });
                        }

                        dgvRecords.DataSource = electricRecordSearchList.OrderBy(x => x.CustomerId).ToList();
                        dgvRecords.Columns[0].ReadOnly = true;
                        dgvRecords.Columns[1].ReadOnly = true;
                        dgvRecords.Columns[2].ReadOnly = true;
                        dgvRecords.Columns[3].ReadOnly = true;
                        dgvRecords.Columns[4].ReadOnly = true;
                        dgvRecords.Columns[5].ReadOnly = true;
                        dgvRecords.Columns[6].ReadOnly = true;
                        dgvRecords.Columns[8].ReadOnly = true;

                        dgvRecords.Columns.Remove("RegisterPlace");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
                else
                {
                    var connection = new OracleConnection(connectionString);
                    try
                    {
                        connection.Open();
                        var cmd = new OracleCommand()
                        {
                            Connection = connection,
                            CommandText = String.Format(@"SELECT e.recordid, c.customerid, c.registerplace, p.name, e.firstpoint, e.lastpoint, c.CMND, t.price, pa.datepaid
                                    FROM customer c JOIN person p ON c.personid = p.personid
                                    JOIN type t ON t.typeid = c.typeid
                                    JOIN electricrecord e ON e.customerid = c.customerid
                                    JOIN payment pa ON pa.recordid = e.recordid
                                    WHERE e.month = {0} and e.year = {1}
                                    UNION
                                    SELECT e.recordid, c.customerid, c.registerplace, p.name, e.firstpoint, e.lastpoint, c.CMND, t.price, pa.datepaid
                                    FROM customer c JOIN person p ON c.personid = p.personid
                                    JOIN type t ON t.typeid = c.typeid
                                    JOIN electricrecord@dblink_elec e ON e.customerid = c.customerid
                                    JOIN payment@dblink_elec pa ON pa.recordid = e.recordid
                                    WHERE e.month = {2} and e.year = {3}", month, year, month, year),
                            CommandType = CommandType.Text
                        };

                        OracleDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            electricRecordSearchList.Add(new ElectricRecord()
                            {
                                RecordId = reader.GetInt32(0),
                                CustomerId = reader.GetInt32(1),
                                RegisterPlace = reader.GetInt32(2),
                                CustomerName = reader.GetString(3),
                                FirstPoint = reader.GetFloat(4),
                                LastPoint = reader.GetFloat(5),
                                CMND = reader.GetString(6),
                                Price = reader.GetFloat(7),
                                PaidDate = reader.GetString(8),
                                Month = month,
                                Year = year
                            });
                        }

                        dgvRecords.DataSource = electricRecordSearchList.OrderBy(x => x.CustomerId).ToList();
                        dgvRecords.Columns[0].ReadOnly = true;
                        dgvRecords.Columns[1].ReadOnly = true;
                        dgvRecords.Columns[2].ReadOnly = true;
                        dgvRecords.Columns[3].ReadOnly = true;
                        dgvRecords.Columns[4].ReadOnly = true;
                        dgvRecords.Columns[5].ReadOnly = true;
                        dgvRecords.Columns[6].ReadOnly = true;
                        dgvRecords.Columns[8].ReadOnly = true;

                        dgvRecords.Columns.Remove("RegisterPlace");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
                if (!String.IsNullOrEmpty(txtSearch.Text))
                {
                    electricRecordSearchList.Clear();
                    foreach (DataGridViewRow row in dgvRecords.Rows)
                    {
                        if (row.Cells[2].Value.ToString().Contains(txtSearch.Text))
                        {
                            electricRecordSearchList.Add(new ElectricRecord()
                            {
                                RecordId = int.Parse(row.Cells[0].Value.ToString()),
                                CustomerId = int.Parse(row.Cells[1].Value.ToString()),
                                RegisterPlace = row.Cells[9].Value.ToString() == "Hai Chau" ? 1 : 2,
                                CustomerName = row.Cells[2].Value.ToString(),
                                FirstPoint = float.Parse(row.Cells[6].Value.ToString()),
                                LastPoint = float.Parse(row.Cells[7].Value.ToString()),
                                CMND = row.Cells[3].Value.ToString(),
                                Price = float.Parse(row.Cells[8].Value.ToString()),
                                Month = int.Parse(row.Cells[4].Value.ToString()),
                                Year = int.Parse(row.Cells[5].Value.ToString())

                            });
                        }
                    }
                    dgvRecords.DataSource = electricRecordSearchList.OrderBy(x => x.CustomerId).ToList();
                    dgvRecords.Columns[0].ReadOnly = true;
                    dgvRecords.Columns[1].ReadOnly = true;
                    dgvRecords.Columns[2].ReadOnly = true;
                    dgvRecords.Columns[3].ReadOnly = true;
                    dgvRecords.Columns[4].ReadOnly = true;
                    dgvRecords.Columns[5].ReadOnly = true;
                    dgvRecords.Columns[6].ReadOnly = true;
                    dgvRecords.Columns[8].ReadOnly = true;

                    dgvRecords.Columns.Remove("RegisterPlace");
                }
            }
            else
            {
                MessageBox.Show("Please choose Month and Year !");
            }
        }


    }

}
